﻿using NDK.Module.Model;
using NDK.Utils;
using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SQLite;
using System.Text;

namespace NDK.Module.DAL
{
    public class AlarmInfoServices
    {
        public void Add(AlarmInfo model)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("insert into AlarmInfo(");
            strSql.Append("ID,StartTime,EndTime,TimeCount,Type,Description)");
            strSql.Append(" values (");
            strSql.Append("@ID,@StartTime,@EndTime,@TimeCount,@Type,@Description)");
            SQLiteParameter[] parameters = {
                    new SQLiteParameter("@ID", DbType.Int32,4),
                    new SQLiteParameter("@StartTime", DbType.DateTime),
                    new SQLiteParameter("@EndTime", DbType.DateTime),
                    new SQLiteParameter("@TimeCount", DbType.Int32,4),
                    new SQLiteParameter("@Type", DbType.String,200),
                    new SQLiteParameter("@Description", DbType.String,200)};
            parameters[1].Value = model.StartTime;
            parameters[2].Value = model.EndTime;
            parameters[3].Value = model.TimeCount;
            parameters[4].Value = model.Type;
            parameters[5].Value = model.Description;

            SQLiteUtils.ExecuteSql(strSql.ToString(), parameters);
        }

        public DataSet GetList(string strWhere)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("select ID,StartTime,EndTime,TimeCount,Type,Description ");
            strSql.Append(" FROM AlarmInfo ");
            if (strWhere.Trim() != "")
            {
                strSql.Append(" where " + strWhere);
            }
            return SQLiteUtils.Query(strSql.ToString());
        }


        public List<AlarmInfo> GetAllModelList()
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("select ID,StartTime,EndTime,TimeCount,Type,Description from AlarmInfo");

            List<AlarmInfo> alarmInfoList = new List<AlarmInfo>();
            DataSet ds = SQLiteUtils.Query(strSql.ToString());


            if (ds.Tables[0].Rows.Count > 0)
            {
                for (int i = 0; i < ds.Tables[0].Rows.Count; i++)
                {
                    AlarmInfo model = new AlarmInfo();

                    if (ds.Tables[0].Rows[i]["ID"].ToString() != "")
                    {
                        model.ID = int.Parse(ds.Tables[0].Rows[i]["ID"].ToString());
                    }
                    if (ds.Tables[0].Rows[i]["StartTime"].ToString() != "")
                    {
                        model.StartTime = DateTime.Parse(ds.Tables[0].Rows[i]["StartTime"].ToString());
                    }
                    if (ds.Tables[0].Rows[i]["EndTime"].ToString() != "")
                    {
                        model.EndTime = DateTime.Parse(ds.Tables[0].Rows[i]["EndTime"].ToString());
                    }
                    if (ds.Tables[0].Rows[i]["TimeCount"].ToString() != "")
                    {
                        model.TimeCount = int.Parse(ds.Tables[0].Rows[i]["TimeCount"].ToString());
                    }
                    if (ds.Tables[0].Rows[i]["Type"].ToString() != "")
                    {
                        model.Type = ds.Tables[0].Rows[i]["Type"].ToString();
                    }
                    if (ds.Tables[0].Rows[i]["Description"].ToString() != "")
                    {
                        model.Description = ds.Tables[0].Rows[i]["Description"].ToString();
                    }

                    alarmInfoList.Add(model);
                }
                return alarmInfoList;
            }
            else
            {
                return null;
            }
        }

        public DataTable GetModelListByStartTimeAndType(DateTime startTime, DateTime endTime, string type)
        {
            StringBuilder strSql = new StringBuilder();

            if (type == "所有报警类型")
            {
                strSql.Append("select ID,StartTime,EndTime,TimeCount,Type,Description from AlarmInfo ");
                strSql.Append($" where StartTime BETWEEN @startTime AND @endTime");

                SQLiteParameter[] parameters = {
                    new SQLiteParameter("@startTime", DbType.DateTime),
                    new SQLiteParameter("@endTime", DbType.DateTime)
                                                   };
                parameters[0].Value = startTime;
                parameters[1].Value = endTime;

                List<AlarmInfo> alarmInfoList = new List<AlarmInfo>();
                DataSet ds = SQLiteUtils.Query(strSql.ToString(), parameters);

                return ds.Tables[0];
            }
            else
            {
                strSql.Append("select ID,StartTime,EndTime,TimeCount,Type,Description from AlarmInfo ");
                strSql.Append($" where Type=@Type and StartTime BETWEEN @startTime AND @endTime");

                SQLiteParameter[] parameters = {
                    new SQLiteParameter("@Type", DbType.String),
                    new SQLiteParameter("@startTime", DbType.DateTime),
                    new SQLiteParameter("@endTime", DbType.DateTime)
                                                     };
                parameters[0].Value = type;
                parameters[1].Value = startTime;
                parameters[2].Value = endTime;

                List<AlarmInfo> alarmInfoList = new List<AlarmInfo>();
                DataSet ds = SQLiteUtils.Query(strSql.ToString(), parameters);

                return ds.Tables[0];
            }
        }


        public float GetAlarmTimeCountByStartTimeAndType(DateTime startTime, DateTime endTime, string type)
        {
            float timeCount = 0;
            StringBuilder strSql = new StringBuilder();

            if (type == "所有报警类型")
            {
                strSql.Append("select ID,StartTime,EndTime,TimeCount,Type,Description from AlarmInfo ");
                strSql.Append($" where StartTime BETWEEN @startTime AND @endTime");

                SQLiteParameter[] parameters = {
                    new SQLiteParameter("@startTime", DbType.DateTime),
                    new SQLiteParameter("@endTime", DbType.DateTime)
                                                   };
                parameters[0].Value = startTime;
                parameters[1].Value = endTime;

                List<AlarmInfo> alarmInfoList = new List<AlarmInfo>();
                DataSet ds = SQLiteUtils.Query(strSql.ToString(), parameters);

                if (ds.Tables[0].Rows.Count > 0)
                {
                    for (int i = 0; i < ds.Tables[0].Rows.Count; i++)
                    {
                        AlarmInfo model = new AlarmInfo();

                        if (ds.Tables[0].Rows[i]["ID"].ToString() != "")
                        {
                            model.ID = int.Parse(ds.Tables[0].Rows[i]["ID"].ToString());
                        }
                        if (ds.Tables[0].Rows[i]["StartTime"].ToString() != "")
                        {
                            model.StartTime = DateTime.Parse(ds.Tables[0].Rows[i]["StartTime"].ToString());
                        }
                        if (ds.Tables[0].Rows[i]["EndTime"].ToString() != "")
                        {
                            model.EndTime = DateTime.Parse(ds.Tables[0].Rows[i]["EndTime"].ToString());
                        }
                        if (ds.Tables[0].Rows[i]["TimeCount"].ToString() != "")
                        {
                            model.TimeCount = int.Parse(ds.Tables[0].Rows[i]["TimeCount"].ToString());
                        }
                        if (ds.Tables[0].Rows[i]["Type"].ToString() != "")
                        {
                            model.Type = ds.Tables[0].Rows[i]["Type"].ToString();
                        }
                        if (ds.Tables[0].Rows[i]["Description"].ToString() != "")
                        {
                            model.Description = ds.Tables[0].Rows[i]["Description"].ToString();
                        }

                        alarmInfoList.Add(model);
                    }

                    foreach (var item in alarmInfoList)
                    {
                        timeCount += item.TimeCount;
                    }
                }

                if (timeCount / 60 <= 11)
                {
                    return timeCount / 60;
                }
                else
                {
                    return 11;
                }

            }
            else
            {
                strSql.Append("select ID,StartTime,EndTime,TimeCount,Type,Description from AlarmInfo ");
                strSql.Append($" where Type=@Type and StartTime BETWEEN @startTime AND @endTime");

                SQLiteParameter[] parameters = {
                    new SQLiteParameter("@Type", DbType.String),
                    new SQLiteParameter("@startTime", DbType.DateTime),
                    new SQLiteParameter("@endTime", DbType.DateTime)
                                                     };
                parameters[0].Value = type;
                parameters[1].Value = startTime;
                parameters[2].Value = endTime;

                List<AlarmInfo> alarmInfoList = new List<AlarmInfo>();
                DataSet ds = SQLiteUtils.Query(strSql.ToString(), parameters);

                if (ds.Tables[0].Rows.Count > 0)
                {
                    for (int i = 0; i < ds.Tables[0].Rows.Count; i++)
                    {
                        AlarmInfo model = new AlarmInfo();

                        if (ds.Tables[0].Rows[i]["ID"].ToString() != "")
                        {
                            model.ID = int.Parse(ds.Tables[0].Rows[i]["ID"].ToString());
                        }
                        if (ds.Tables[0].Rows[i]["StartTime"].ToString() != "")
                        {
                            model.StartTime = DateTime.Parse(ds.Tables[0].Rows[i]["StartTime"].ToString());
                        }
                        if (ds.Tables[0].Rows[i]["EndTime"].ToString() != "")
                        {
                            model.EndTime = DateTime.Parse(ds.Tables[0].Rows[i]["EndTime"].ToString());
                        }
                        if (ds.Tables[0].Rows[i]["TimeCount"].ToString() != "")
                        {
                            model.TimeCount = int.Parse(ds.Tables[0].Rows[i]["TimeCount"].ToString());
                        }
                        if (ds.Tables[0].Rows[i]["Type"].ToString() != "")
                        {
                            model.Type = ds.Tables[0].Rows[i]["Type"].ToString();
                        }
                        if (ds.Tables[0].Rows[i]["Description"].ToString() != "")
                        {
                            model.Description = ds.Tables[0].Rows[i]["Description"].ToString();
                        }

                        alarmInfoList.Add(model);
                    }

                    foreach (var item in alarmInfoList)
                    {
                        timeCount += item.TimeCount;
                    }
                }

                if (timeCount / 60 <= 11)
                {
                    return timeCount / 60;
                }
                else
                {
                    return 11;
                }
            }
        }


        /// <summary>
        /// 删除超过时间time的数据
        /// </summary>
        public bool DeleteBeforeTime(DateTime time)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("delete from AlarmInfo ");
            strSql.Append(" where StartTime<@time ");
            SQLiteParameter[] parameters = {
                    new SQLiteParameter("@time", DbType.DateTime)};
            parameters[0].Value = time;
            int rows = SQLiteUtils.ExecuteSql(strSql.ToString(), parameters);

            System.Threading.Thread.Sleep(2000);
            strSql = new StringBuilder();
            strSql.Append("vacuum ");
            rows = SQLiteUtils.ExecuteSql(strSql.ToString());
            if (rows > 0)
            {
                return true;
            }
            else
            {
                return false;
            }
        }

    }
}
